const mysql = require('mysql')
let connection
const connect = (config) => {
    connection = mysql.createConnection(config)
    connection.connect()
}
const insert = (tableName, item) => {
    let keys = ''
    let params = [null]
    let span = ''
    for (let key in item) {
        keys += key + ',' 
        params.push(item[key])
        span += '?,'
    }
    keys = keys.substring(0,keys.length-1)
    span = span.substring(0,span.length-1)
    let addSql = `INSERT INTO ${tableName}(Id,${keys}) VALUES(?,${span})`
    return new Promise((resolve, reject) => {
        connection.query(addSql, params, function (err, result) {
            if (err) {
                reject(err)
            } else {
                resolve(result.insertId);
            }
        })
    })
}
const save = (tableName, item) => {
    if (!item.id) {
        return insert(tableName, item)
    }
    return exists(tableName,item)
        .then(res => {
            if (res) {
                return update(tableName, item)
            } else {
                return insert(tableName, item)
            }
        })
}
const desctory = () => {
    connection.end()
}
const update = (tableName, item) => {
    let keys = ''
    let params = []
    for (let key in item) {
        keys += key + ' = ?,' 
        params.push(item[key])
    }
    keys = keys.substring(0,keys.length-1)
    return new Promise((resolve, reject) => {
        connection.query('UPDATE '+tableName+' SET '+keys+' WHERE Id = '+item.id,params, (err, res) => {
            if (err) {
                reject(err)
            } else {
                if (res.length > 0) {
                    resolve(true)
                } else {
                    resolve(false)
                }
            }
        })
    })
}
const query = (queryString, params) => {
    return new Promise((resolve, reject) => {
        connection.query(queryString, params,function (err,res){
            if (err) {
                reject(err)
            } else {
                resolve(res)
            }
        }) 
    })
}
const exists = (tableName, item) => {
    let keys = ''
    let params = []
    for (let key in item) {
        keys += key + ' = ? and ' 
        params.push(item[key])
    }
    keys = keys.substring(0,keys.length-5)
    return new Promise((resolve, reject) => {
        connection.query('SELECT * FROM ' +tableName+' WHERE ' +keys+' LIMIT 0,1', params,function (err,res){
            if (err) {
                reject(err)
            } else {
                if (res.length > 0) {
                    resolve(res[0].id)
                } else {
                    resolve(false)
                }
            }
        }) 
    }) 
}

/**
 * 
 * @param {String} tableName 表名
 * @param {Object} item 查询条件 
 */
const get = (tableName, item) => {
    let keys = ''
    let params = []
    for (let key in item) {
        keys += key + ' = ? and ' 
        params.push(item[key])
    }
    keys = keys.substring(0,keys.length-5)
    return new Promise((resolve, reject) => {
        connection.query('SELECT * FROM ' + tableName + ' WHERE ' + keys, params,function (err,res){
            if (err) {
                reject(err)
            } else {
                if (res.length > 0) {
                    resolve(res[0])
                } else {
                    resolve(false)
                }
            }
        }) 
    })
}
const existsAndGet = (tableName, item) => {
    let keys = ''
    let params = []
    for (let key in item) {
        keys += key + ' = ? and ' 
        params.push(item[key])
    }
    keys = keys.substring(0,keys.length-5)
    return new Promise((resolve, reject) => {
        connection.query('SELECT * FROM ' +tableName+' WHERE ' +keys+' LIMIT 0,1', params,function (err,res){
            if (err) {
                reject(err)
            } else {
                if (res.length > 0) {
                    resolve(res[0])
                } else {
                    resolve(false)
                }
            }
        }) 
    }) 
}
module.exports = {
    connection,
    insert,
    exists,
    existsAndGet,
    connect,
    save,
    query,
    desctory,
    update
}